Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Query tuning with connection and startup parameters

In addition to controlling aspects of how the DataServer handles queries programmatically within 4GL statements, you can control the same aspects through startup and connection parameter options.

Note: Startup and connection parameters override query-tuning defaults. Options set in the QUERY-TUNING phrase take precedence over startup and connection parameters. For example, if you specify “qt_debug,SQL” at connection it will override the NO-DEBUG default. If you additionally specify NO-DEBUG for a specific query, the NO-DEBUG for the query will override the connection setting, and no SQL will be written to the log file for that query. In this example, the DataServer will write a report that includes all the SQL it generates for the application, except for the query with the NO-DEBUG option. See the "Query tuning" section for more information.

You override query-tuning defaults with the DataServer (-Dsrv) connection parameter when you connect to the ORACLE database. The -Dsrv switch accepts a comma separated list of options and values. To guarantee correct parsing, there can be no spaces in the list., as shown in the following syntax:

Syntax

CONNECT db-name -U user-name -P password -Dsrv query-tuning-option1,value1, query-tuning-option2,value2.

Table 6–5 describes the query-tuning options that you can specify with the -Dsrv parameter. Unless otherwise indicated, these options apply at compile and run time.

Table 6–5: Connection query-tuning options 

Option	Description
qt_bind_where qt_no_bind_where	Specifies whether the DataServer uses ORACLE bind variables for values in WHERE clauses. Specify qt_no_bind_where to use literals. Use at run time only. Default: qt_bind_where.
qt_cache_size,integer, QT_BYTE qt_cache_size,integer, QT_ROW	Specifies the size of the cache (in bytes or records) for information used by lookahead or standard cursors. Byte maximum: 65535 bytes. Byte minimum: Specify the number of bytes contained in a single record. For joins, specify the number of bytes contained in two joined records. If a join returns a 500-byte record, you need a cache of 1000 bytes. Row maximum: The number of records that can fit in 65535 bytes. See the "Caching records" section for more information. Row minimum: 1. If the server performs the join, the minimum is 2. If QT_BYTE or QT_ROW is omitted, QT_BYTE is assumed. Default: 1024 bytes with standard cursors; 8192 with lookahead cursors.
qt_lookahead qt_no_lookahead	Specifies whether the DataServer uses lookahead or standard cursors. Specify qt_no_lookahead for behavior that is consistent with OpenEdge. Default: qt_lookahead, except with statements that use an EXCLUSIVE lock.

The following example shows how to use the query-tuning options to enhance performance. The multiple records that the lookahead cursors require are stored in a 32K cache. In addition, the DataServer writes an extended report on the SQL statements it executes, as shown:

CONNECT oradb -U user -P password -Dsrv qt_cache_size,32000,qt_debug,EXTENDED.

Use startup parameters to override two other query-tuning defaults, INDEX-HINT and JOIN-BY-SQLDB. Table 6–6 describes these startup parameters.

Table 6–6: Query-tuning startup parameters

Startup Parameter	Description
Index Hint (-noindexhint)	Specifies that the DataServer not provide index hints to the ORACLE DBMS. Generally index hints improve performance, but ORACLE’s responses to hints vary between releases. Use -noindexhint to test whether performance for a query improves when the DataServer executes it without hints. Use -noindexhint at compile or run time.
Server Join (-nojoinbysqldb)	Specifies that the client evaluates and performs queries that have joins. This might slow performance, but provides results that are consistent with OpenEdge behavior. Use -nojoinbysqldb at compile time. It has no effect at run time. Note: Server joins are never performed for dynamic queries.
Get Previous (-Dsrv srv-get-prev)	The addition of this parameter allows for queries defined with qt_no_lookahead to correctly execute a GET PREV after a reposition. Without this switch, when a reposition fails to find the matching record in either the client or server cache, and new SQL is generated to satisfy the query, the resulting record is reset to the starting point of the query, rendering the GET PREV meaningless.